package com.hebust.mapper;

import com.hebust.utils.student.*;
import org.apache.ibatis.annotations.*;
import org.springframework.web.bind.annotation.DeleteMapping;

import java.util.List;

@Mapper
public interface StudentMapper {

    // 选修课程的增删查

    @Select("SELECT co_id as id,\n" +
            "co_name as cname,\n" +
            "t_name as tname,\n" +
            "co_teacher_id as tid,\n" +
            "co_credit as credit,\n" +
            "co_grade as grade,\n" +
            "co_time as ttime,\n" +
            "co_location as location,\n" +
            "co_select_count as sselect,\n" +
            "co_max_size as max\n" +
            "from course,teachers\n" +
            "where co_teacher_id = t_id\n" +
            "and co_name like concat('%',#{name},'%')")
    List<SelectCourse> show_selectcourse(@Param("name") String name);

    @Insert("INSERT INTO studentscore(sc_student_id,sc_course_id,sc_teacher_name,sc_teacher_id) VALUES(#{sid},#{cid},#{name},#{tid})")
    Integer add_selectcourse(int sid, int cid, String name, int tid);

    @Update("update course\n" +
            "set co_select_count = (co_select_count+1)\n" +
            "where co_id = #{id}")
    Integer update_select(@Param("id") int id);


    @Select("SELECT \n" +
            "sc_id as result\n" +
            "from studentscore\n" +
            "where sc_student_id = #{sid}\n" +
            "and sc_course_id = #{id}")
    List<SelectResult> select_course(@Param("id") int id, @Param("sid") int sid);

    @Insert("INSERT INTO teacherscore(student_id,teacher_id,course_id) VALUES(#{sid},#{tid},#{cid})")
    Integer insert_teacherscore(@Param("sid") int sid, @Param("tid") int tid, @Param("cid") int cid);

    @Select("SELECT \n" +
            "c_grade as grade\n" +
            "from students,class\n" +
            "where s_id = #{sid}\n" +
            "and s_class_id = c_id")
    Integer select_grade(@Param("sid") int sid);


    // 选修课程列表
    @Select("SELECT\n" +
            "co_name as cname,\n" +
            "t_name as tname,\n" +
            "co_credit as credit,\n" +
            "co_time as ttime,\n" +
            "co_location as location\n" +
            "FROM course,teachers\n" +
            "where co_id in (SELECT\n" +
            "sc_course_id\n" +
            "from studentscore\n" +
            "where sc_student_id = #{sid})\n" +
            "and co_teacher_id = t_id")
    List<StudentCourse> show_studentcourse(@Param("sid") int sid);

    @Select("SELECT\n" +
            "sc_id as id,\n" +
            "sc_course_id as cid \n" +
            "from studentscore\n" +
            "where sc_course_id = (SELECT\n" +
            "co_id as cid\n" +
            "from course\n" +
            "where co_name = #{name} )\n" +
            "and sc_student_id = #{sid}")
    StudentCourseResult search_id(@Param("sid") int sid, @Param("name") String name);

    @Delete("DELETE from studentscore\n" +
            "WHERE sc_id = #{id}")
    Integer delete_studentcourse(@Param("id") int id);

    @Update("update course\n" +
            "set co_select_count = (co_select_count-1)\n" +
            "where co_id = #{id}")
    Integer update_studentcourse(@Param("id") int cid);

    @Delete("DELETE from teacherscore where student_id = #{sid} and course_id = #{cid}")
    Integer delete_teacherscore(int sid, int cid);

    // 考试地点查询

    @Select("SELECT\n" +
            "co_name as cname,\n" +
            "t_name as tname,\n" +
            "co_exam_date as dtime,\n" +
            "co_exam_location as location\n" +
            "FROM course,teachers\n" +
            "where co_id in (SELECT\n" +
            "sc_course_id\n" +
            "from studentscore\n" +
            "where sc_student_id = #{sid})\n" +
            "and co_teacher_id = t_id")
    List<StudentExam> show_exam(@Param("sid") int sid);

    // 教师评测

    @Select("SELECT \n" +
            "co_id as id,\n" +
            "co_name as cname,\n" +
            "t_name as tname,\n" +
            "teacher_score as score\n" +
            "from teacherscore,course,teachers\n" +
            "WHERE student_id = #{sid} \n" +
            "and course_id = co_id\n" +
            "and co_teacher_id = t_id")
    List<TeacherScore> show_teacherscore(@Param("sid") int sid);

    @Update("update teachers\n" +
            "set score_stu = ((score_stu + #{score})/2),score_count = (score_count + 1)\n" +
            "where t_id = (SELECT co_teacher_id from course where co_id = #{id})")
    Integer update_teacherscore(int id, int score);

    @Update("update teacherscore \n" +
            "set teacher_score = #{score}\n" +
            "where student_id = #{sid}\n" +
            "and course_id = #{cid}")
    Integer update_score(@Param("sid") int sid, @Param("cid") int cid, @Param("score") int score);

    // 成绩查询
    @Select("select co_name as cname,\n" +
            "sc_teacher_name as tname,\n" +
            "co_credit as credit,\n" +
            "sc_score as score\n" +
            "from studentscore,course\n" +
            "where sc_course_id = co_id\n" +
            "and sc_student_id = #{sid}")
    List<ScoreSearch> show_score(@Param("sid") int sid);

    // 修改密码

    @Update("update students\n" +
            "set s_password = #{password}\n" +
            "where s_id = #{sid}")
    Integer update_studentpassword(@Param("sid") int sid, @Param("password") String password);

    // 课表展示
    @Select("SELECT \n" +
            "co_name as cname,\n" +
            "co_time as ttime,\n" +
            "co_location as location\n" +
            "from course,studentscore\n" +
            "WHERE sc_student_id = #{sid}\n" +
            "and sc_course_id = co_id")
    List<Course> show_courseschedule(@Param("sid") int sid);

}
